# -*- coding:utf-8 -*-
# @Time : 2020/1/9 16:13
# @Author: liangkun
# @File : auto_test02
# @Des : 自动化测试之读写数据库

# import pip._internal
# print(pip._internal.pep425tags.get_supported())
# 如果查找不到，查看匹配的版本，通过改名
# [('cp38', 'cp38m', 'win_amd64'), ('cp38', 'none', 'win_amd64'), ('py3', 'none', 'win_amd64')
# 找whl 改名 ---  mysqlclient-1.4.6-cp38-none-win_amd64.whl

import MySQLdb, random, uuid
from datetime import datetime

a1 = ['张', '金', '李', '王', '赵']

a2 = ['玉', '明', '龙', '芳', '军', '玲']

a3 = ['', '立', '玲', '', '国', '']

conn = MySQLdb.connect(host='localhost', user='root', password='123456', port=3306, charset='utf8', database='test02')
cursor = conn.cursor()


def getName():
    return random.choice(a1) + random.choice(a2) + random.choice(a3)


def checkbyname(sql, name):
    cursor.execute(sql)
    for i in range(cursor.rowcount):
        row = cursor.fetchone()
        # rows = cursor.fetchmany(20)
        # print(rows)
        # print(row)
        if row[1] == name:
            print('找到检查点... ', 'id ' + row[0] + ' name ' + row[1])


def insert(sql, id):
    findsql = 'select * from student where card_id=' + id
    print('insert... ', 'findsql ' + findsql)
    cursor.execute(findsql)
    count = cursor.rowcount
    if count > 0:
        row = cursor.fetchone()
        print('有该数据... ', 'id ' + row[0] + ' name ' + row[1])
        return
    cursor.execute(sql, ['4290042008198601545', '梁坤', 25, '湖北仙桃', datetime.now().strftime('%Y-%m-%d %H:%M:%S')])
    # 插入后提交，否则插入不成功
    conn.commit()
    print('。。。。。。。。。。... ')
    for i in range(cursor.rowcount):
        row2 = cursor.fetchone()
        if row2 is not None:
            if row2[0] == id:
                print('插入成功... ', 'id ' + row2[0] + ' name ' + row2[1])
                pass


def insertmany():
    sql = "insert into student (card_id, person_name,person_age,person_address,person_birth) values (%s, %s, %s, %s,%s)"
    for i in range(1000):
        cursor.execute(sql, [uuid.uuid1(), getName(), random.randint(1, 40), '湖北仙桃',
                             datetime.now().strftime('%Y-%m-%d %H:%M:%S')])
    conn.commit()


# 如果数据量大（几百万条），可能会把崩溃
# rows = cursor.fetchall()
# print(rows)
if __name__ == '__main__':
    # 查询mysql的名字
    sql = 'select * from student'
    name = '梁坤'
    checkbyname(sql, name)
    birth = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    # 插入单个数据
    print('-----------------------插入数据------------------------------ ')
    sqlinsert = "insert into student (card_id, person_name,person_age,person_address,person_birth) values (%s, %s, %s, %s,%s)"
    insert(sqlinsert, '4290042008198601545')
    # 插入1万条数据
    # insertmany()
    sqll = 'select count(*) from student'
    cursor.execute(sqll)
    count = cursor.fetchall()
    print('总数量', count[0][0])
    cursor.close()
    conn.close()
